package com.orangelife.service.propFeeAccount.impl;

import com.orangelife.exception.AppBizException;
import com.orangelife.redis.core.redis.session.redisSession.WebServiceSession;
import com.orangelife.service.Importlogs.QiniuUtils;
import com.orangelife.service.propFeeAccount.IPropFeeAccountService;
import com.orangelife.service.propFeeAccountImportLog.IPropFeeAccountImportLogService;
import com.orangelife.service.propFeeAccountImportLog.model.PropFeeAccountImportLogDto;
import com.orangelife.service.propFeeApartment.IPropFeeApartmentService;
import com.orangelife.utils.IdUtil;
import com.orangelife.utils.ListSetUtils;
import com.orangelife.utils.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by chenxiang on 2018/5/25.
 */
@Service("importExcel")
public class ImportExcel implements  IImportExcel{

    

    private final String EXCEL_ACCOUNT="account";
    private final String[] ACCOUNT_CELL={"楼栋","单元","房号","固定费用预缴款金额","水电公摊预存款金额","通用预存款金额"};

    private  String [] HOUSES_UNIT={"楼栋","单元","房号"};
    private  String [][] MONEY_UNIT={{"固定费用预缴款金额","FIXED"},{"水电公摊预存款金额","POOL"},{"通用预存款金额","COMMON"}};

    @Resource(name = "propFeeAccountService")
    private IPropFeeAccountService propFeeAccountService;

    @Resource(name = "propFeeAccountImportLogService")
    private IPropFeeAccountImportLogService propFeeAccountImportLogService;

    private QiniuUtils qiniuUtils = new QiniuUtils();

    @Resource(name = "propFeeApartmentService")
    private IPropFeeApartmentService propFeeApartmentService;
    
    
    @Override
    public int importExcel(String commID, Sheet sheet, WebServiceSession w, String fileName,String fileCode) throws AppBizException {
       int code=2;
        switch (fileCode) {
            case EXCEL_ACCOUNT:
                code= importAccount(commID,sheet,w,fileName);
                break;
                default:
                    break;
        }

       

        return code;
    }

    public int importAccount(String commID, Sheet sheet, WebServiceSession webServiceSession, String fileName) throws AppBizException{


        int rowCount = sheet.getPhysicalNumberOfRows();// 获得总行数
        Map<String, Integer> columnMap =null;
        if (rowCount < 1) {
            throw new AppBizException(2, "sheet页无数据");
        }
        //创建日志
        PropFeeAccountImportLogDto cmmImportLog = createAccountImprotLog(fileName);
        propFeeAccountImportLogService.addPropFeeAccountImportLog(cmmImportLog);
        //返回房号信息
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("commID", commID);
        List<Map<String, Object>> houseInfos = propFeeApartmentService
                .getHouseInfo(map);

        //验证表格输出字段
        try {
            columnMap=checkRecordColum(sheet, commID);
        }catch (Exception e){
            return  0;
        }

        Set<Map<String, Object>> recordDtos = repalceExcel(sheet, commID, rowCount,
                columnMap, houseInfos);
        ListSetUtils listSetUtils = new ListSetUtils();
        List<List<Map<String, Object>>> li = listSetUtils.splitList(
                listSetUtils.setToList(recordDtos), 200);
        for (int i = 0; i < li.size(); i++) {
            HandleAccountThread thread = new HandleAccountThread(commID, "线程["
                    + (i) + "] ", li.get(i), propFeeAccountService, webServiceSession,
                    houseInfos);
            thread.start();
        }
        return 2;
    }

    private Set<Map<String,Object>> repalceExcel(Sheet sheet,String  commID,int rowCount,
                                                 Map<String, Integer> columnMap,List<Map<String, Object>> houseInfos) throws  AppBizException{

        Map<Integer,String > logmap =new HashMap<>();
        int errortol = 0;
        int total = 0;
        Set<Map<String, Object>> propFeeMeterRecordDtos = new HashSet<Map<String, Object>>();
        Row firstRow = sheet.getRow(1);
        if (firstRow == null) {
            throw new AppBizException(99, "亲，请最少填写一行信息");
        }

        for (int i = 1; i < rowCount; i++) {
            try {
                Map<String, Object> account = new HashMap<String, Object>();
                Row dataRow = sheet.getRow(i);
                if (dataRow == null) {
                    break;
                }
                 if (valExcelRowisNull( dataRow, columnMap)) {
                    logmap.put(i + 1, "该行出现了的空行，导入停止");
                    break;

                }else if (valExcelRowCheck(dataRow,columnMap, houseInfos, logmap, propFeeMeterRecordDtos))
                 {
                     errortol++;
                     continue;
                 }


                propFeeMeterRecordDtos=rankConversion(dataRow,columnMap,HOUSES_UNIT,MONEY_UNIT,propFeeMeterRecordDtos);
                total++;
            } catch (AppBizException e) {
                e.printStackTrace();
                logmap.put(i + 1, "行数据转换错误,错误原因：" + e.getMessage());
                errortol++;

            }
        }


        return propFeeMeterRecordDtos;
    }

    private  Set<Map<String, Object>> rankConversion(Row dataRow,Map<String, Integer> columnMap,String [] hosStr,String [][] fromat,Set<Map<String, Object>> setmap)
            throws  AppBizException{
        Map<String ,Object> account=null;
        for(int i=0;i<fromat.length;i++){
            String accountCnName=fromat[i][0]; //钱包中文名
            String accountCode=fromat[i][1]; //钱包code
            String buildingName=hosStr[0]; //楼栋
            String unitName=hosStr[1]; //单元
            String apartmentName=hosStr[2];//房号
            if(StringUtils.isBlank(this.getCellValue(
                    dataRow.getCell(columnMap.get(accountCnName))))){
                continue;
            }
            account=new HashMap<>();

            account.put("buildingName",
                    getCellValue(dataRow.getCell(columnMap.get(buildingName))));


            account.put("apartmentName",
                    this.getCellValue(dataRow.getCell(columnMap.get(apartmentName))));

            String  unit_Name = this.getCellValue(dataRow.getCell(columnMap
                    .get(unitName)));

            if (!StringUtils.isBlank(unit_Name)) {
                account.put("unitName", unit_Name);
            } else {
                account.put("unitName", null);
            }

            account.put("rowNum", dataRow.getRowNum());

            account.put("money",this.getCellValue(
                    dataRow.getCell(columnMap.get(accountCnName)), "0.##"));
            account.put("account_type_code",accountCode);
            setmap.add(account);
        }

        return  setmap;
    }

    private boolean valExcelRowisNull(Row dataRow,
                                      Map<String, Integer> columnMap) throws AppBizException{
        String [] str={"楼栋","单元","房号"};

        if (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
                .get(str[0]))))
                && StringUtils.isBlank(this.getCellValue(dataRow
                .getCell(columnMap.get(str[1]))))
                && StringUtils.isBlank(this.getCellValue(dataRow
                .getCell(columnMap.get(str[2]))))) {
                return true;
        }

        return  false;
    }

    /**
     * 验证楼栋 单元 房号 内容是否为空 三个金额是否一起为空
     * @param dataRow
     * @param columnMap
     * @param houseInfos
     * @param logmap
     * @param propFeeMeterRecordDtos
     * @return
     * @throws AppBizException
     */
    private boolean valExcelRowCheck(Row dataRow,
                                      Map<String, Integer> columnMap,
                                      List<Map<String, Object>> houseInfos,Map<Integer,String > logmap,
                                      Set<Map<String, Object>> propFeeMeterRecordDtos) throws AppBizException{
        int rownum=dataRow.getRowNum()+1;
        if (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
                .get(HOUSES_UNIT[0]))))){
            logmap.put(rownum, "亲，第" + rownum + "行楼栋为空");
            return true;
        }

        if (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
                .get(HOUSES_UNIT[1]))))) {
            logmap.put(rownum, "亲，第" + rownum + "行房号为空");
            return true;
        }

        if (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
                .get(HOUSES_UNIT[2]))))) {
            logmap.put(rownum, "亲，第" + rownum + "行是金额为空");
            return true;
        }

        if(StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap.get(MONEY_UNIT[0][0]))))
                &&StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap.get(MONEY_UNIT[1][0]))))
                &&StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap.get(MONEY_UNIT[2][0]))))){
            logmap.put(rownum, "亲，第" + rownum + "行是金额不能都为空");
            return true;
        }
        boolean isCheck = false;
        String unitName = this
                .getCellValue(dataRow.getCell(columnMap.get("单元")));
        String buildingName = this.getCellValue(dataRow.getCell(columnMap
                .get("楼栋")));
        String apartmentName = this.getCellValue(dataRow.getCell(columnMap
                .get("房号")));

        if (propFeeMeterRecordDtos != null && propFeeMeterRecordDtos.size() > 0) {
            boolean isExIst = false;
            String row = "";
            for (Map<String, Object> map : propFeeMeterRecordDtos) {
                if (StringUtils.isBlank(unitName)) {
                    if (buildingName.equals(map.get("buildingName").toString())
                            && apartmentName.equals(map.get("apartmentName")
                            .toString())) {
                        isExIst = true;
                        row = map.get("rowNum").toString();
                        break;
                    }
                } else {
                    if (map.get("buildingName").toString().equals(buildingName)
                            && map.get("apartmentName").toString()
                            .equals(apartmentName)
                            && unitName.equals(map.get("unitName").toString())) {
                        isExIst = true;
                        row = map.get("rowNum").toString();
                        break;
                    }
                }

            }
            if (isExIst) {
                logmap.put(rownum, "亲，第"
                        + rownum
                        + "行 楼栋"
                        + buildingName
                        + "-"
                        + (StringUtils.isBlank(unitName) ? "" : "单元" + unitName
                        + "-") + "房号为" + apartmentName + "房屋信息与" + row
                        + "行信息重复");
                return true;
            }
        }
        for (Map<String, Object> house : houseInfos) {
            if (StringUtils.isBlank(unitName)) {
                if (house.get("buildingName").toString().equals(buildingName)
                        && house.get("apartmentNum").toString()
                        .equals(apartmentName)
                        && StringUtils.isBlank(StringUtils.toString(house
                        .get("unitName")))) {
                    isCheck = true;
                    break;
                }
            } else {
                if (house.get("buildingName").toString().equals(buildingName)
                        && house.get("apartmentNum").toString()
                        .equals(apartmentName)
                        && unitName.equals(StringUtils.toString(house.get("unitName")))) {
                    isCheck = true;
                    break;
                }
            }

        }
        if (!isCheck) {
            logmap.put(rownum, "亲，第"
                    + rownum
                    + "行 楼栋"
                    + buildingName
                    + "-"
                    + (StringUtils.isBlank(unitName) ? "" : "单元" + unitName
                    + "-") + "房号为" + apartmentName + "房屋信息在该小区不存在");
            return true;
        }


        return  false;
    }

    /**
     * cell内容处理
     * @param cell
     * @return
     * @throws AppBizException
     */
    private String getCellValue(Cell cell) throws AppBizException {
        String cellValue = null;
        try {
            if (cell != null) {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            SimpleDateFormat format = new SimpleDateFormat(
                                    "yyyy-MM-dd HH:mm:ss");
                            cellValue = format.format(date);

                        } else {
                            DecimalFormat df = new DecimalFormat("#");
                            cellValue = df.format(cell.getNumericCellValue());

                        }
                    }
                    break;
                    case Cell.CELL_TYPE_STRING: {
                        cellValue = cell.getStringCellValue().trim();
                    }
                    break;

                    default:
                        break;
                }
            }
        } catch (Exception e) {
            throw new AppBizException(99, e.getMessage());
        }
        return cellValue;
    }

    private String getCellValue(Cell cell, String format1)
            throws AppBizException {
        String cellValue = null;
        try {
            if (cell != null) {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            SimpleDateFormat format = new SimpleDateFormat(
                                    "yyyy-MM-dd HH:mm:ss");
                            cellValue = format.format(date);

                        } else {
                            DecimalFormat df = new DecimalFormat(
                                    format1 == null ? "#" : format1);
                            cellValue = df.format(cell.getNumericCellValue());
                            // cellValue = String.valueOf((int) cell
                            // .getNumericCellValue());
                        }
                    }
                    break;
                    case Cell.CELL_TYPE_STRING: {
                        cellValue = cell.getStringCellValue().trim();
                    }
                    break;

                    default:
                        break;
                }
            }
        } catch (Exception e) {
            throw new AppBizException(99, e.getMessage());
        }
        return cellValue;
    }
    /**
     * 验证表格内容并返回信息
     * @param sheet
     * @param commID
     * @return
     * @throws AppBizException
     */
    private Map<String, Integer> checkRecordColum(Sheet sheet, String commID)
            throws AppBizException {
        Boolean status=true;
        String error=null;
        Row row = sheet.getRow(0);// 第一行
        int columnCount = row.getLastCellNum();// 获取列数
        Map<String, Integer> columnMap = new HashMap<String, Integer>();

        for(int j=0 ;j<ACCOUNT_CELL.length;j++){
                String cellName=ACCOUNT_CELL[j];
                for (int i = 0; i < columnCount; i++) {
                    String columnName = row.getCell((short) i).getStringCellValue()
                            .trim();
                    if (columnName.equals(cellName)){
                            columnMap.put(columnName, i);
                    }
                }

                if(!columnMap.containsKey(cellName)){
                    status=false;
                    error="亲,使用正确的excel的格式(缺少"+cellName+")";
                    break;
                }

        }

        if(!status){
            propFeeAccountImportLogService
                    .editPropFeeAccountImportLog(failureAccountImprotLog(commID,error));
            throw new AppBizException(99, error);
        }
        return columnMap;
    }

    /**
     * 创建日志
     * @param fileName
     * @return
     */
    private PropFeeAccountImportLogDto createAccountImprotLog(String fileName){
        PropFeeAccountImportLogDto cmmImportLog = new PropFeeAccountImportLogDto();
        cmmImportLog.setCreateuserID("1");
        cmmImportLog.setImportLogID(IdUtil.getuuId());
        cmmImportLog.setItemStat("1");
        cmmImportLog.setStatus("1");
        cmmImportLog.setTscreate(new Date());
        cmmImportLog.setTsedit(new Date());
        cmmImportLog.setFileName(fileName);
        return  cmmImportLog;
    }

    private PropFeeAccountImportLogDto failureAccountImprotLog(String commID,String error){
        PropFeeAccountImportLogDto cmmImportLog = new PropFeeAccountImportLogDto();

        Map <Integer,String > logmap=new TreeMap<Integer, String>();
        logmap.put(1, error);
        /****
         * 添加失败日志
         */
        cmmImportLog.setStatus("3");
        cmmImportLog.setImportInfo("成功0条");
        String logurl = qiniuUtils.createErrorLogFile(logmap);
        cmmImportLog.setLink(logurl);
        cmmImportLog.setFinishTime(new Date());
        cmmImportLog.setTsedit(new Date());
        cmmImportLog.setCommID(commID);

        return  cmmImportLog;
    }

    public Set<Map<String, Object>>  replaceSheet(Sheet sheet, String commID,
                                                  int rowCount, Map<String, Integer> columnMap,
                                                  List<Map<String, Object>> houseInfos,
                                                  PropFeeAccountImportLogDto cmmImportLog, Map<Integer, String> logmap)throws AppBizException{


            return  null;
    }


}
